#include <stdio.h>
#include <sqlite3.h>

void insert_student(sqlite3 *db, int id, const char *name, int age)
{
	int   ret;
	char *errmsg;
	char  sql[1024];

	sprintf(sql, "insert into student values(%d,'%s',%d);", id, name, age);
	ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "Fail to sqlite3_exec %s : %s\n", sql, errmsg);
		return;
	}

	return;
}

int display_student_info(void *arg, int n_colum, char **pvalue, char **pname)
{

	int i;
	//字段名
	for(i = 0; i < n_colum; i++)
	{
		printf("%s ", pname[i]);
	}
	printf("\n");
	for(i = 0; i < n_colum; i++)
	{
		printf("%s ", pvalue[i]);
	}
	printf("\n");

	return 0;
}

void query_student(sqlite3 *db)
{
	int   ret;
	char *errmsg;
	char  sql[] = "select * from student;";
	ret         = sqlite3_exec(db, sql, display_student_info, NULL, &errmsg);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "Fail to sqlite3_exec %s : %s\n", sql, errmsg);
		return;
	}
}

void show_table(char **presult, int n_row, int n_column)
{
	int i     = 0;
	int j     = 0;
	int index = n_column;

	for(i = 0; i < n_column; i++)
	{
		printf("%s\t", presult[i]);
	}
	printf("\n");

	for(i = 0; i < n_row; i++)
	{
		for(j = 0; j < n_column; j++)
		{
			printf("%s\t", presult[index++]);
		}
		printf("\n");
	}
}

void query_student_table(sqlite3 *db)
{
	int    ret;
	char  *errmsg;
	char **presult;
	int    n_row = 0, n_column = 0;
	char   sql[] = "select * from student";
	ret          = sqlite3_get_table(db, sql, &presult, &n_row, &n_column, &errmsg);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "Fail to sqlite3_get_table %s : %s\n", sql, errmsg);
		return;
	}

	if(n_row)
	{
		show_table(presult, n_row, n_column);
	}
	sqlite3_free_table(presult);

	return;
}

void prepare_insert_student(sqlite3 *db)
{
	int           ret;
	int           i;
	sqlite3_stmt *pstmt;
	const char   *pTail;
	char          name[100];
	char         *sql = "insert into student values(?,?,?);";
	ret               = sqlite3_prepare(db, sql, -1, &pstmt, &pTail);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "Fail to sqlite3_prepare %s : %s\n", sql, pTail);
		return;
	}
	for(i = 0; i < 10; i++)
	{
		ret = sqlite3_bind_int(pstmt, 1, 100 + i);
		printf("ret = %d\n", ret);
		sprintf(name, "name%d", i);
		sqlite3_bind_text(pstmt, 2, name, -1, SQLITE_STATIC);
		sqlite3_bind_int(pstmt, 3, 18 + i);

		ret = sqlite3_step(pstmt);
		if(ret != SQLITE_DONE)
		{
			fprintf(stderr, "Fail to sqlite3_step\n");
			break;
		}

		sqlite3_reset(pstmt);
	}

	sqlite3_finalize(pstmt);
	return;
}

void display_table_row(sqlite3_stmt *pstmt)
{
	int   id   = sqlite3_column_int(pstmt, 0);
	char *name = (char *)sqlite3_column_text(pstmt, 1);
	int   age  = sqlite3_column_int(pstmt, 2);
	printf("ID:%d,Name:%s,Age:%d\n", id, name, age);
	return;
}

void prepare_query_student_table(sqlite3 *db)
{
	int           ret;
	sqlite3_stmt *pstmt;
	const char   *pTail;
	char          name[100];
	char         *sql = "select * from student;";
	ret               = sqlite3_prepare(db, sql, -1, &pstmt, &pTail);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "Fail to sqlite3_prepare %s : %s\n", sql, pTail);
		return;
	}

	while(1)
	{
		ret = sqlite3_step(pstmt);
		if(ret == SQLITE_DONE)
		{
			break; //执行完成
		}
		else if(ret == SQLITE_ROW)
		{
			display_table_row(pstmt);
		}
		else
		{
			fprintf(stderr, "Fail to sqlite3_step,err code%d\n", ret);
			break;
		}
	}

	sqlite3_finalize(pstmt);

	return;
}

int main(int argc, char const *argv[])
{
	int           ret;
	const char   *database = "test.db";
	sqlite3      *db;
	char         *sql;
	sqlite3_stmt *pstmt;
	char         *pTail;

	ret = sqlite3_open(database, &db);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "Fail to sqlite3_open:%s\n", sqlite3_errmsg(db));
		return -1;
	}

#if 0
	prepare_insert_student(db);
	insert_student(db, 123, "XYZ", 80);
	prepare_insert_student(db);
	query_student(db);
	query_student_table(db);
#endif

	prepare_query_student_table(db);
	sqlite3_close(db);

	return 0;
}
